package com.hsh.tools.dynamic;

import com.hsh.entity.SysExcelMergeCell;
import com.hsh.tools.DateUtils2;
import com.hsh.tools.StringUtils;
import com.hsh.tools.uuid.IdUtils;
import com.hsh.vo.result.dynamic.TitleAll;
import lombok.Data;
import lombok.experimental.Accessors;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * excel读写工具类
 */
public abstract class POIUtil {

    private static final Logger logger = LoggerFactory.getLogger(POIUtil.class);


    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * EXCEL 至多只能识别 EXCEL_LETTER.length 列 数据
     */
    private static final String[] EXCEL_LETTER = {
            "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "Z", "Y", "Z",
            "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AZ", "AY", "AZ"};

    @Data
    @Accessors(chain = true)
    public static class ResultBase {
        // 是否存在
        private boolean flag;
        // 当前范围的所有制【含首尾】
        private List<String> letterRangeData;
    }

    /**
     * 查找当前数据是否在指定范围【转大写匹配】
     *
     * @param todoValue  查询的值
     * @param rangeLeft  范围起点
     * @param rangeRight 范围终点
     * @return bool
     */
    public static ResultBase specifiedRangeFind(String todoValue, String rangeLeft, String rangeRight) {
        if (StringUtils.isEmpty(todoValue)) {
            throw new RuntimeException("查询的值不能为空！");
        }
        if (StringUtils.isEmpty(rangeLeft)) {
            throw new RuntimeException("范围起点不能为空！");
        }
        if (StringUtils.isEmpty(rangeRight)) {
            throw new RuntimeException("范围终点不能为空！");
        }
        Set<String> set = new HashSet<>(Arrays.asList(EXCEL_LETTER));
        if (!set.contains(rangeLeft.toUpperCase())) {
            throw new RuntimeException("范围起点不合法！");
        }
        if (!set.contains(rangeRight.toUpperCase())) {
            throw new RuntimeException("范围终点不合法！");
        }
        if (rangeLeft.toUpperCase().hashCode() > rangeRight.toUpperCase().hashCode()) {
            throw new RuntimeException("范围起点不能大于范围终点！");
        }
        int rangeLeftIndex = 0;
        int rangeRightIndex = 0;
        for (int i = 0; i < EXCEL_LETTER.length; i++) {
            if (EXCEL_LETTER[i].equals(rangeLeft)) {
                rangeLeftIndex = i;
            }
            if (EXCEL_LETTER[i].equals(rangeRight.toUpperCase())) {
                rangeRightIndex = i;
                // 范围终点获取到时起点已经有值
                break;
            }
        }
        List<String> data = Arrays.asList(EXCEL_LETTER).subList(rangeLeftIndex, rangeRightIndex + 1);

        ResultBase result = new ResultBase();
        result.setFlag(data.contains(todoValue.toUpperCase())).setLetterRangeData(data);
        return result;
    }

    //

    /**
     * dynamic excel template export
     *
     * @param sheetName     工作表的名称
     * @param colLength     列数
     * @param headTitle,    大标题info
     * @param headMergeCell 大标题merge info
     * @param record        标题数据集合
     */
    public static synchronized void exportDynamicExcelTemplate(String sheetName, int colLength, String headTitle, SysExcelMergeCell headMergeCell, List<TitleAll> record) {
        try {

            // 1.创建一个workbook,对应一个excel文件
            Workbook wb = new HSSFWorkbook();
            // 2.在workbook中添加一个sheet,对应Excel中的sheet
            Sheet sheet = wb.createSheet(sheetName);
            // 创建列
            for (int i = 0; i < colLength; i++) {
                sheet.setColumnWidth(i, 256 * 15);
            }
            // 3.设置样式以及字体样式
            CellStyle headerStyle = PoiStyleUtils.createHeadCellStyle(wb);
            CellStyle titleStyle = PoiStyleUtils.createTitleCellStyle(wb);
            CellStyle contentStyle = PoiStyleUtils.createContentCellStyle(wb);
            // 4.创建标题，合并标题单元格
            int rowNum = 0;
            // 创建第一行，索引从0开始(标题行)
            String head = MessageFormat.format(headTitle, DateUtils2.getYear(), DateUtils2.getMonth(true), DateUtils2.getDay(true));
            Row row0 = sheet.createRow(rowNum++);
            // 设置行高
            row0.setHeight((short) 800);
            Cell c00 = row0.createCell(0);
            c00.setCellValue(head);
            c00.setCellStyle(headerStyle);
            sheet.addMergedRegion(new CellRangeAddress(headMergeCell.getFirstRow(), headMergeCell.getFirstRow(), headMergeCell.getFirstCol(), headMergeCell.getLastCol()));

            Map<Integer, List<TitleAll>> recordMap = record.stream().collect(Collectors.groupingBy(TitleAll::getRow));
            // 多少行标题
            List<Integer> rowDistinct = record.stream().map(TitleAll::getRow).distinct().collect(Collectors.toList());

            // 记录当前excel 模板中有几个标题，及每行标题所对应的下标.
            // 标识array 长度为几即表示标题就拥有几列【excel-> 三部分  【1、大标题】【2、标题】【3、具体内容】{局限于上下盘版的数据格式}
            int[] titleInfo = new int[rowDistinct.size()];
            for (int i = 0; i < rowDistinct.size(); i++) {
                // 标题的下标
                int thisRowIndex = rowNum + i;
                titleInfo[i] = thisRowIndex;
            }
            // 第一个[N] 标识有N 行数据
            // 第一个[2] 有2 个值,第一个值为行的下标，第二个值为当前行列的下标   其最终value == titleInfo.length
            int[][] positionRowCol = new int[titleInfo.length][2];
            for (int i = 0; i < positionRowCol.length; i++) {
                for (int j = 0; j < positionRowCol[i].length; j++) {
                    positionRowCol[i][0] = titleInfo[i];
                    positionRowCol[i][1] = 0;
                }
            }
            // TODO 待优化的功能！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
            // 行遍历
            // 循环遍历N 行'标题'内容
            for (int z = 0; z < rowDistinct.size(); z++) {
                Row row = sheet.createRow(positionRowCol[z][0]);
                row.setHeight((short) 500);
                List<TitleAll> titleAllVos = recordMap.get(rowDistinct.get(z));
                for (TitleAll node : titleAllVos) {
                    // 开始行索引
                    Integer firstRow = node.getFirstRow();
                    // 结束行索引
                    Integer lastRow = node.getLastRow();
                    // 开始列索引
                    Integer firstCol = node.getFirstCol();
                    // 结束列索引
                    Integer lastCol = node.getLastCol();

                    if (!Objects.equals(firstRow, lastRow) && firstRow != null) {// 需要 ‘上下’合并单元格
                        Cell cell1 = row.createCell(positionRowCol[z][1]);
                        // 记录下一个列的下表位置
                        positionRowCol[z][1] += 1;

                        cell1.setCellValue(node.getTitleName());
                        cell1.setCellStyle(titleStyle);
                        int i = lastRow - firstRow;
                        for (int j = 0; j < i; j++) {
                            // 获取下一行title 的坐标信息
                            Row row2 = sheet.createRow(positionRowCol[z + j + 1][0]);
                            row2.setHeight((short) 500);
                            Cell cell2 = row2.createCell(positionRowCol[z + j + 1][1]);
                            positionRowCol[z + j + 1][1] += 1;

                            cell2.setCellValue("");
                            cell2.setCellStyle(titleStyle);

                        }
                        sheet.addMergedRegion(new CellRangeAddress(node.getFirstRow(), node.getLastRow(), node.getFirstCol(), node.getLastCol()));
                    } else if (!Objects.equals(firstCol, lastCol) && firstCol != null) { // 需要 ’左右‘合并单元格
                        Cell cell1 = row.createCell(positionRowCol[z][1]);
                        positionRowCol[z][1] += 1;

                        cell1.setCellValue(node.getTitleName());
                        cell1.setCellStyle(titleStyle);
                        int i = lastCol - firstCol;
                        for (int j = 0; j < i; j++) {
                            Cell cell2 = row.createCell(positionRowCol[z][1]);
                            positionRowCol[z][1] += 1;

                            cell2.setCellValue("");
                            cell2.setCellStyle(titleStyle);

                        }
                        sheet.addMergedRegion(new CellRangeAddress(node.getFirstRow(), node.getLastRow(), node.getFirstCol(), node.getLastCol()));
                    } else if (!Objects.equals(firstRow, lastRow) && !Objects.equals(firstCol, lastCol)) { // 需要 ‘上下左右’均存在合并单元格现象
                        if (!Objects.equals(firstRow, lastRow) && firstRow != null) {
                            Cell cell = row.createCell(positionRowCol[z][1]);
                            positionRowCol[z][1] += 1;

                            cell.setCellValue(node.getTitleName());
                            cell.setCellStyle(titleStyle);
                            int i = lastRow - firstRow;
                            for (int j = 0; j < i; j++) {
                                Row row2 = sheet.createRow(positionRowCol[z + j + 1][0]);
                                row2.setHeight((short) 500);
                                Cell tempCell2 = row2.createCell(positionRowCol[z + j + 1][1]);
                                positionRowCol[z + j + 1][1] += 1;

                                tempCell2.setCellValue("");
                                tempCell2.setCellStyle(titleStyle);

                            }
                            sheet.addMergedRegion(new CellRangeAddress(node.getFirstRow(), node.getLastRow(), node.getFirstCol(), node.getLastCol()));
                        }
                        if (!Objects.equals(firstCol, lastCol) && firstCol != null) {
                            // 需要 ’左右‘合并单元格
                            Cell cell = row.createCell(positionRowCol[z][1]);
                            positionRowCol[z][1] += 1;

                            cell.setCellValue(node.getTitleName());
                            cell.setCellStyle(titleStyle);

                            int i = lastCol - firstCol;
                            for (int j = 0; j < i; j++) {
                                Cell tempCell2 = row.createCell(positionRowCol[z][1]);
                                positionRowCol[z][1] += 1;

                                tempCell2.setCellValue("");
                                tempCell2.setCellStyle(titleStyle);

                            }
                            sheet.addMergedRegion(new CellRangeAddress(node.getFirstRow(), node.getLastRow(), node.getFirstCol(), node.getLastCol()));
                        }

                    } else {
                        // 普通单元格
                        Cell cell = row.createCell(positionRowCol[z][1]);
                        positionRowCol[z][1] += 1;

                        cell.setCellValue(node.getTitleName());
                        cell.setCellStyle(titleStyle);
                    }
                }
            }

//            // 需要和并的单元格信息
//            List<TitleAllVo> mergeRecord = record.stream().filter(i -> i.getMergeId() != null && i.getFirstRow() != null && i.getLastRow() != null && i.getFirstCol() != null & i.getLastCol() != null).collect(Collectors.toList());
//            if (StringUtils.isNotEmpty(mergeRecord)) {
//                for (TitleAllVo node : mergeRecord) {
//                    sheet.addMergedRegion(new CellRangeAddress(node.getFirstRow(), node.getLastRow(), node.getFirstCol(), node.getLastCol()));
//                }
//            }

            // todo 生成唯一ID
            String filePath = "F:\\Test\\demo\\dispose\\" + IdUtils.randomUUID() + URLEncoder.encode(sheetName, StandardCharsets.UTF_8.toString()) + "." + XLSX;
            try (OutputStream out = new FileOutputStream(filePath)) {
                wb.write(out);
            } catch (Exception e) {
                logger.error("文件导出错误 {0}", e);
                e.printStackTrace();
            } finally {
                wb.close();
            }
        } catch (Exception e) {
            logger.error("dynamic import error msg {0}", e);
            e.printStackTrace();
        }
    }

    /**
     * @param file     待读excel文件
     * @param sheetNum 第几个工作表
     */
    @SuppressWarnings("all")
    public static synchronized RecordExcelData readExcel(MultipartFile file, Integer sheetNum) throws IOException {
        // 获取工作表
        Sheet sheet = getSheet(file, sheetNum);
        // 获得当前sheet的开始行
        int firstRowNum = sheet.getFirstRowNum();
        // 获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        // 第一行Data
        Row disposeRow = sheet.getRow(0);
        if (Objects.isNull(disposeRow)) {
            throw new RuntimeException("请使用合法模板进行导入sheet 数据不能为空！");
        }
        int columnWidth = disposeRow.getPhysicalNumberOfCells();
        if (columnWidth <= 1) {
            throw new RuntimeException("大标题的合并单元格有误！，请重新合并后再进行导入，谢谢！");
        }
        // 需要合并的列record
        List<CellRangeAddress> combineCell = getCombineCell(sheet);
        // 头的单元格
        List<CellRangeAddress> addressList = combineCell.stream().filter(cellAddresses -> cellAddresses.getLastColumn() + 1 == columnWidth && cellAddresses.getFirstColumn() == 0).collect(Collectors.toList());
        if (addressList.size() == 0) {
            throw new RuntimeException("请使用合法模板进行导入！");
        }
        if (addressList.size() != 1) {
            throw new RuntimeException("excel 表头head 信息校验失败，请联系相关人员！");
        }

        // head 标题至少占一行 1+
        int headRowspan = addressList.get(0).getLastRow() - addressList.get(0).getFirstRow();
        // title 标题至少占一行 1+
        // 获取title 上下合并最宽的数据
        CellRangeAddress titleMaxWidth = combineCell.stream().filter(cellAddresses -> !cellAddresses.formatAsString().equals(addressList.get(0).formatAsString())).max(Comparator.comparingInt(o -> (o.getLastRow() - o.getFirstRow()))).get();

        int lastTitleRow = 0;
        if (titleMaxWidth == null) {
            lastTitleRow = headRowspan + 1;
        } else {
            lastTitleRow = headRowspan + (titleMaxWidth.getLastRow() - titleMaxWidth.getFirstRow()) + 1;
        }

        RecordExcelData result = new RecordExcelData();
        Map<String, CellRangeAddress> cellRangeAddressMap = new HashMap<>(combineCell.size());
        for (CellRangeAddress node : combineCell) {
            cellRangeAddressMap.put(node.formatAsString(), node);
        }
        // 标题map
        Map<Integer, List<RangeAddress>> tMap = new HashMap<>();
        // 内容map
        Map<Integer, List<RangeAddress>> cMap = new HashMap<>();

        // loop excel
        for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {

            // 获得当前行
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            // 获得当前行的开始列
            int firstCellNum = row.getFirstCellNum();
            // 获得当前行的列数
            int lastCellNum = row.getPhysicalNumberOfCells();
            if (lastCellNum > EXCEL_LETTER.length) {
                throw new RuntimeException("EXCEL 至多只能解析：" + EXCEL_LETTER.length + "列 数据！");
            }
            List<RangeAddress> rangeAddressesTitle = new ArrayList<>();
            List<RangeAddress> rangeAddressesContent = new ArrayList<>();

            // 获取标题
            if (rowNum <= lastTitleRow) {
                for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    CellAddress address = cell.getAddress();
                    String value = getCellValue(cell);
                    //  每行数据中控制不予返回【不对excel内容进行处理】
                    if (StringUtils.isEmpty(value)) {
                        continue;
                    }
                    // 获取headName
                    if (rowNum == 0 && cellNum == 0) {
                        RangeAddress rangeAddress = new RangeAddress();
                        rangeAddress.setName(getCellValue(row.getCell(0))).setCellAddress(address.toString()).setRow(address.getRow()).setCol(address.getColumn()).setRowspan(headRowspan + 1);
                        result.setHeadName(rangeAddress);
                    }
                    RangeAddress rangeAddress = new RangeAddress();
                    rangeAddress.setName(value).setCellAddress(address.toString()).setRow(address.getRow()).setCol(address.getColumn());
                    rangeAddressesContent.add(rangeAddress);
                }
                if (StringUtils.isNotEmpty(rangeAddressesContent)) {
                    tMap.put(rowNum, rangeAddressesContent);
                }
            } else {
                for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    CellAddress address = cell.getAddress();
                    String value = getCellValue(cell);

                    RangeAddress rangeAddress = new RangeAddress();
                    rangeAddress.setName(value).setCellAddress(address.toString()).setRow(address.getRow()).setCol(address.getColumn());
                    rangeAddressesTitle.add(rangeAddress);
                }
                cMap.put(rowNum, rangeAddressesTitle);
            }

        }
        result.setCellRangeAddressMap(cellRangeAddressMap);
        result.setTitleMap(tMap);
        result.setContentMap(cMap);
        return result;
    }

    /**
     * 读入excel文件，解析后返回
     */
    public static RecordExcelData readExcel(MultipartFile file) throws IOException {
        return readExcel(file, 0);
    }


    /**
     * @param sheet 工作单
     * @return @return 返回合并区域的数据
     */
    private static List<CellRangeAddress> getCombineCell(Sheet sheet) {
        //  获得指定 sheet 中合并单元格的数量
        int sheetMergerCount = sheet.getNumMergedRegions();
        List<CellRangeAddress> list = new ArrayList<>(sheetMergerCount);
        for (int i = 0; i < sheetMergerCount; i++) {
            list.add(sheet.getMergedRegion(i));
        }
        return list;
    }

    /**
     * @param combineCell 存放合并单元格的list
     * @param cell        需要判断的单元格
     * @param sheet       sheet
     * @return 判断单元格是否为合并单元格，是的话则将单元格的值返回
     */
    private String checkCombineCell(List<CellRangeAddress> combineCell, Cell cell, Sheet sheet)
            throws Exception {
        int firstC = 0, lastC = 0, firstR = 0, lastR = 0;
        String cellValue = null;
        for (CellRangeAddress ca : combineCell) {
            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
                if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
                    Row fRow = sheet.getRow(firstR);
                    Cell fCell = fRow.getCell(firstC);
                    cellValue = getCellValue(fCell);
                    break;
                }
            } else {
                cellValue = "";
            }
        }
        return cellValue;
    }

    /**
     * 获取工作表
     */
    private static Sheet getSheet(MultipartFile file, Integer sheetNum) throws IOException {
        // 1、检查文件
        checkExcelFile(file);
        // 2、获得Workbook工作薄对象
        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        // check sheet
        if (sheetNum + 1 > workbook.getNumberOfSheets()) {
            throw new IOException("请传入有效的工作表下标！");
        }
        // 获得当前sheet工作表
        Sheet sheet = workbook.getSheetAt(sheetNum);
        workbook.close();
        return sheet;
    }

    /**
     * 检查是否为 excel 文件
     *
     * @param file
     * @throws IOException
     */
    @SuppressWarnings("all")
    private static void checkExcelFile(MultipartFile file) throws IOException {
        //判断文件是否存在
        if (null == file) {
            logger.error("文件不存在！");
            throw new FileNotFoundException("文件不存在！");
        }
        String fileName = file.getOriginalFilename();
        if (!fileName.toLowerCase().endsWith(XLS) && !fileName.toLowerCase().endsWith(XLSX)) {
            logger.error(fileName + "不是excel文件");
            throw new IOException(fileName + "不是excel文件");
        }
    }


    /**
     * 获取单元格的值（获取不同类型，都返回String）
     */
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (Objects.nonNull(cell)) {
            switch (cell.getCellType()) {
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = DateUtils2.parseDateToStr(DateUtils2.YYYY_MM_DD_HH_MM_SS, cell.getDateCellValue());
                    } else {
                        // 有些数字过大，直接输出使用的是科学计数法： 2.67458622E8 要进行处理
                        DecimalFormat df = new DecimalFormat("####.####");
                        cellValue = df.format(cell.getNumericCellValue());
                    }
                    break;
                case STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    cellValue = String.valueOf(Double.valueOf(cell.getNumericCellValue()));
                    break;
                default:
                    cellValue = "";
                    break;
            }
        }
        return cellValue;
    }

    /**
     * 解析excel的data
     */
    @Data
    @Accessors(chain = true)
    public static class RecordExcelData {


        /**
         * 当前excel 中合并的单元格信息
         * k：A?:A?
         * v：当前行的数据集
         */
        private Map<String, CellRangeAddress> cellRangeAddressMap;


        /**
         * 指定 sheet 的所有数据
         * k：行【不是指下表】
         * v：当前行的数据集对象
         */
        private Map<Integer, List<RangeAddress>> titleMap;

        /**
         * 指定 sheet 的所有数据
         * k：行【不是指下表】
         * v：当前行的数据集对象
         */
        private Map<Integer, List<RangeAddress>> contentMap;


        /**
         * excel 模板名称
         */
        private RangeAddress headName;

    }

    @Data
    @Accessors(chain = true)
    private static class RangeAddress {

        // 当前单元格名称
        private String name;

        // 当前内容的position
        private String cellAddress;

        // 行索引(第一行为0) Row index (first row is 0)
        private Integer row;

        // 列索引(第一行为0)Column index (first column is 0)
        private Integer col;

        // 占用行数
        private Integer rowspan;

    }

}
